﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:SYS_SURVEYTMPSET
	/// </summary>
	public partial class SYS_SURVEYTMPSET : BaseDAL<GSLMS.Entity.TB_SYS_SURVEYTMPSET>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public SYS_SURVEYTMPSET()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_SYS_SURVEYTMPSET model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_SURVEYNAME", model.SURVEYNAME??""),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID),
								new OracleParameter("P_SURVEYTYPE", model.SURVEYTYPE),
								new OracleParameter("P_CREATEBY", model.CREATEBY??""),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE),
                                new OracleParameter("returnvalue",OracleType.Int32)
                     };
  
			for (int i = 0; i < 6; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

            parms[6].Direction = ParameterDirection.Output;

            try
            {
                OracleConnection con = new OracleConnection(OracleHelper.connectionString);
                con.Open();
                OracleTransaction tran = con.BeginTransaction();
                OracleCommand cmd = con.CreateCommand();
                cmd.Transaction = tran;

                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parms);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "pkg_sys.proc_surveytmpset_add";

                cmd.ExecuteNonQuery();
                long num = 0;

                if (parms[6].Value != DBNull.Value)
                    num = OracleHelper.ToLong(parms[6].Value.ToString());
                if (num > 0)
                {
                    flag = AddDetail(ref cmd, model.DetailList, num);
                    if (flag)
                    {
                        tran.Commit();
                        //清除缓存
                        OracleHelper.RemoveCacheObject("_SurveyTmpSetList");
                    }
                    else
                    {
                        tran.Rollback();
                    }

                }
                else
                {
                    tran.Rollback();
                }



                cmd.Dispose();
                tran.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }

			
			return flag;
		}

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_SYS_SURVEYTMPSET model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_SURVEYNAME", model.SURVEYNAME??""),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID),
								new OracleParameter("P_SURVEYTYPE", model.SURVEYTYPE),
								new OracleParameter("P_CREATEBY", model.CREATEBY??""),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE)};
  
			for (int i = 0; i < 6; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

            try
            {
                OracleConnection con = new OracleConnection(OracleHelper.connectionString);
                con.Open();
                OracleTransaction tran = con.BeginTransaction();
                OracleCommand cmd = con.CreateCommand();
                cmd.Transaction = tran;

                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parms);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "pkg_sys.proc_surveytmpset_update";

               int num=cmd.ExecuteNonQuery();
                
                if (num > 0)
                {
                    flag = AddDetail(ref cmd, model.DetailList, model.ID);
                    if (flag)
                    {
                        tran.Commit();
                        //清除缓存
                        OracleHelper.RemoveCacheObject("_SurveyTmpSetList");
                    }
                    else
                    {
                        tran.Rollback();
                    }

                }
                else
                {
                    tran.Rollback();
                }



                cmd.Dispose();
                tran.Dispose();
                con.Close();
                con.Dispose();
            }
            catch (Exception ex)
            {
                log.ExceptionHand(ex);
            }


			
			return flag;
		}

        private bool AddDetail(ref OracleCommand cmd, List<GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL> list, long pid)
        {
            bool flag = false;
            if (list != null && list.Count > 0)
            {
                foreach (var model in list)
                {
                    OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_SURVEYTMPSETID", pid),
								new OracleParameter("P_SURVEYNAME", model.SURVEYNAME??""),
								new OracleParameter("P_SORTNO", model.SORTNO),
								new OracleParameter("P_TMPITEMNAME", model.TMPITEMNAME??""),
                                new OracleParameter("P_TMPTYPE", model.TMPTYPE)
                      };

                    for (int i = 0; i < 6; i++)
                    {
                        parms[i].Direction = ParameterDirection.Input;
                    }


                    cmd.Parameters.Clear();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "pkg_sys.proc_surveytmpsetdetail_add";
                    cmd.Parameters.AddRange(parms);

                    try
                    {
                        int num = cmd.ExecuteNonQuery();
                        if (num != 1)
                        {
                            flag = false;
                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        log.ExceptionHand(ex);
                    }


                }
                flag = true;
            }
            else
            {
                flag = true;
            }


            return flag;
        }

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_surveytmpset_delete", parms);
				flag = true;
                //清除缓存
                OracleHelper.RemoveCacheObject("_SurveyTmpSetList");
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_SYS_SURVEYTMPSET DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_SYS_SURVEYTMPSET model = new GSLMS.Entity.TB_SYS_SURVEYTMPSET();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["SURVEYNAME"]!=null && dr["SURVEYNAME"].ToString()!="")
				{
					model.SURVEYNAME=dr["SURVEYNAME"].ToString();
				}
				if(dr["SYSTEMID"]!=null && dr["SYSTEMID"].ToString()!="")
				{
                    model.SYSTEMID = OracleHelper.ToInt(dr["SYSTEMID"].ToString());
				}
				if(dr["SURVEYTYPE"]!=null && dr["SURVEYTYPE"].ToString()!="")
				{
                    model.SURVEYTYPE = OracleHelper.ToInt(dr["SURVEYTYPE"].ToString());
				}
				if(dr["CREATEBY"]!=null && dr["CREATEBY"].ToString()!="")
				{
					model.CREATEBY=dr["CREATEBY"].ToString();
				}
				if(dr["CREATEDATE"]!=null && dr["CREATEDATE"].ToString()!="")
				{
					model.CREATEDATE=DateTime.Parse(dr["CREATEDATE"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

        /// <summary>
        ///DataRowToModel 
        /// </summary>
        private GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL DataRowToDetailModel(DataRow dr)
        {
            if (dr != null && dr.ItemArray.Length > 0)
            {
                GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL model = new GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL();
                if (dr["ID"] != null && dr["ID"].ToString() != "")
                {
                    model.ID = OracleHelper.ToLong(dr["ID"].ToString());
                }
                if (dr["SURVEYTMPSETID"] != null && dr["SURVEYTMPSETID"].ToString() != "")
                {
                    model.SURVEYTMPSETID = OracleHelper.ToLong(dr["SURVEYTMPSETID"].ToString());
                }
                if (dr["SURVEYNAME"] != null && dr["SURVEYNAME"].ToString() != "")
                {
                    model.SURVEYNAME = dr["SURVEYNAME"].ToString();
                }
                if (dr["SORTNO"] != null && dr["SORTNO"].ToString() != "")
                {
                    model.SORTNO = OracleHelper.ToInt(dr["SORTNO"].ToString());
                }
                if (dr["TMPITEMNAME"] != null && dr["TMPITEMNAME"].ToString() != "")
                {
                    model.TMPITEMNAME = dr["TMPITEMNAME"].ToString();
                }
                if (dr["TMPTYPE"] != null && dr["TMPTYPE"].ToString() != "")
                {
                    model.TMPTYPE = OracleHelper.ToInt(dr["TMPTYPE"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_SYS_SURVEYTMPSET GetByID(long ID)
		{
			GSLMS.Entity.TB_SYS_SURVEYTMPSET sModel = new GSLMS.Entity.TB_SYS_SURVEYTMPSET();
		
            OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", ID),
                                new OracleParameter("p_cur", OracleType.Cursor),
                                new OracleParameter("p_dcur", OracleType.Cursor)
                };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;
            parms[2].Direction = ParameterDirection.Output;


            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_surveytmpset_get", parms);
			if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				sModel = DataRowToModel(ds.Tables[0].Rows[0]);
                if (ds.Tables.Count > 1 && ds.Tables[1].Rows.Count > 0)
                {
                    var detailList = new List<GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL>();
                    DataTable dt = ds.Tables[1];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL dModel = new GSLMS.Entity.TB_SYS_SURVEYTMPSETDETAIL();
                        dModel = DataRowToDetailModel(dt.Rows[i]);
                        detailList.Add(dModel);
                    }
                    sModel.DetailList = detailList;
                }
			}
			else
			{
			return null;
			}
			return sModel;
		}

        /// <summary>
        /// 从缓存中获取材料清单设置信息
        /// </summary>
        /// <returns></returns>
        private DataTable GetAllByCache()
        {
            DataTable dt = null;
            if (OracleHelper.GetCachecObject("_SurveyTmpSetList") != null)
                dt = (DataTable)OracleHelper.GetCachecObject("_SurveyTmpSetList");
            if (dt == null || dt.Rows.Count == 0)
            {
                string sql = "";
                sql = "select * from TB_SYS_SURVEYTMPSET order by id desc";
                DataSet ds = OracleHelper.ExecuteDataset(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    OracleHelper.AddCacheObject("_SurveyTmpSetList", (object)dt, 0);
                    //清除缓存
                    //OracleHelper.RemoveCacheObject("_SurveyTmpSetList");
                }

            }

            return dt;
        }

        /// <summary>
        /// 通过调查系统类别和报告类别提取列表信息
        /// </summary>
        /// <param name="systemID">当为小于等于0时提取所有的1典当，2信贷，3担保</param>
        /// <param name="surveyType">小于等于0提取所有,1</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_SURVEYTMPSET> GetListBySurveyType(int systemID,int surveyType)
        {
            var list = new List<GSLMS.Entity.TB_SYS_SURVEYTMPSET>();
            DataTable dt = null;
            string sqlWhere = " 1=1 ";
            if (systemID > 0)
                sqlWhere += " and SYSTEMID=" + systemID.ToString();

            if (surveyType > 0)
                sqlWhere += " and SURVEYTYPE=" + surveyType.ToString();


            dt = OracleHelper.DataTableFileter(GetAllByCache(), sqlWhere, " id desc");

            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_SURVEYTMPSET model = new GSLMS.Entity.TB_SYS_SURVEYTMPSET();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }

            return list;
        }


		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_SYS_SURVEYTMPSET> GetList(GSLMS.Entity.TB_SYS_SURVEYTMPSET searchParam)
		{
			var list=new List<GSLMS.Entity.TB_SYS_SURVEYTMPSET>();
			DataTable dt = null;
			string tblName = "TB_SYS_SURVEYTMPSET";
			string sqlWhere="";
			string strSort="";
			int pageSize=-1;
			int pageIndex=1;
			string strFields="*";
			int totalCount = 0;
			int countFlag = 1;
			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_SYS_SURVEYTMPSET model = new GSLMS.Entity.TB_SYS_SURVEYTMPSET();
					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			
			return list;
		}

		#endregion  Method
	}
}

